using System;
using System.Collections.Generic;
using System.Data.SQLite;
using System.IO;
using System.Management;

namespace WeChatFileManagementWinApp
{
    /// <summary>
    /// 数据库管理类，负责所有数据库相关的操作
    /// </summary>
    public class DatabaseManager
    {
        private readonly string _dbPath;
        private readonly string _connectionString;

        /// <summary>
        /// 初始化数据库管理器
        /// </summary>
        /// <param name="dbPath">数据库文件路径</param>
        public DatabaseManager(string dbPath)
        {
            _dbPath = dbPath;
            _connectionString = $"Data Source={dbPath};Version=3;";
            EnsureDatabaseExists();
        }

        /// <summary>
        /// 确保数据库文件存在并创建必要的表
        /// </summary>
        private void EnsureDatabaseExists()
        {
            string dir = Path.GetDirectoryName(_dbPath);
            if (!Directory.Exists(dir))
            {
                Directory.CreateDirectory(dir);
            }

            if (!File.Exists(_dbPath))
            {
                SQLiteConnection.CreateFile(_dbPath);
            }

            using (var connection = new SQLiteConnection(_connectionString))
            {
                connection.Open();
                CreateTables(connection);
            }
        }

        /// <summary>
        /// 创建数据库表
        /// </summary>
        private void CreateTables(SQLiteConnection connection)
        {
            // 创建搜索目录表
            string createSearchDirectoriesTable = @"
                CREATE TABLE IF NOT EXISTS SearchDirectories (
                    DirectoryPath TEXT PRIMARY KEY
                )";
            ExecuteNonQuery(connection, createSearchDirectoriesTable);

            // 创建搜索结果表
            string createSearchResultsTable = @"
                CREATE TABLE IF NOT EXISTS SearchResults (
                    FilePath TEXT NOT NULL,
                    ComputerName TEXT NOT NULL,
                    FileName TEXT NOT NULL,
                    FileExtension TEXT NOT NULL,
                    SearchDate DATETIME NOT NULL,
                    FileSize INTEGER NOT NULL,
                    LastModified DATETIME NOT NULL,
                    IconData BLOB,
                    PRIMARY KEY (FilePath, ComputerName)
                )";
            ExecuteNonQuery(connection, createSearchResultsTable);
        }

        /// <summary>
        /// 执行非查询SQL命令
        /// </summary>
        private void ExecuteNonQuery(SQLiteConnection connection, string sql, SQLiteTransaction transaction = null)
        {
            using (var command = new SQLiteCommand(sql, connection, transaction))
            {
                command.ExecuteNonQuery();
            }
        }

        /// <summary>
        /// 保存搜索目录列表
        /// </summary>
        public void SaveSearchDirectories(List<string> directories)
        {
            using (var connection = new SQLiteConnection(_connectionString))
            {
                connection.Open();
                using (var transaction = connection.BeginTransaction())
                {
                    try
                    {
                        // 清空现有目录
                        ExecuteNonQuery(connection, "DELETE FROM SearchDirectories", transaction);

                        // 插入新目录
                        string insertSql = "INSERT OR IGNORE INTO SearchDirectories (DirectoryPath) VALUES (@DirectoryPath)";
                        using (var command = new SQLiteCommand(insertSql, connection, transaction))
                        {
                            var parameter = command.CreateParameter();
                            parameter.ParameterName = "@DirectoryPath";
                            command.Parameters.Add(parameter);

                            foreach (var dir in directories)
                            {
                                parameter.Value = dir;
                                command.ExecuteNonQuery();
                            }
                        }
                        transaction.Commit();
                    }
                    catch
                    {
                        transaction.Rollback();
                        throw;
                    }
                }
            }
        }

        /// <summary>
        /// 加载搜索目录列表
        /// </summary>
        public List<string> LoadSearchDirectories()
        {
            var directories = new List<string>();
            using (var connection = new SQLiteConnection(_connectionString))
            {
                connection.Open();
                string selectSql = "SELECT DirectoryPath FROM SearchDirectories";
                using (var command = new SQLiteCommand(selectSql, connection))
                {
                    using (var reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            directories.Add(reader.GetString(0));
                        }
                    }
                }
            }

            // 如果没有目录，返回默认目录
            if (directories.Count == 0)
            {
                directories.Add(Path.Combine(
                    Environment.GetFolderPath(Environment.SpecialFolder.UserProfile),
                    "Documents",
                    "WeChat Files"
                ));
                var searcher = new ManagementObjectSearcher("SELECT * FROM Win32_UserAccount WHERE LocalAccount=True");
                foreach (ManagementObject queryObj in searcher.Get())
                {
                    string username = queryObj["Name"]?.ToString();
                    if (!string.IsNullOrWhiteSpace(username))
                    {
                        var _path = $"C:\\Users\\{username}\\Documents\\WeChat Files";
                        if (Directory.Exists(_path)&& !directories.Contains(_path))
                        {
                            directories.Add(_path);
                        }
                    }
                }
            }
            return directories;
        }

        /// <summary>
        /// 保存搜索结果
        /// </summary>
        public void SaveSearchResults(List<WeChatFile> files)
        {
            string computerName = Environment.MachineName; // 获取当前计算机名
            using (var connection = new SQLiteConnection(_connectionString))
            {
                connection.Open();
                using (var transaction = connection.BeginTransaction())
                {
                    try
                    {
                        // 插入新数据，使用 INSERT OR IGNORE 来避免重复插入
                        string insertSql = @"
                            INSERT OR IGNORE INTO SearchResults 
                            (FileName, FilePath, ComputerName, FileExtension, SearchDate, FileSize, LastModified, IconData)
                            VALUES (@FileName, @FilePath, @ComputerName, @FileExtension, @SearchDate, @FileSize, @LastModified, @IconData)";

                        using (var command = new SQLiteCommand(insertSql, connection, transaction))
                        {
                            foreach (var file in files)
                            {
                                // 检查文件是否已存在
                                string checkSql = "SELECT COUNT(*) FROM SearchResults WHERE FilePath = @FilePath AND ComputerName = @ComputerName";
                                using (var checkCommand = new SQLiteCommand(checkSql, connection, transaction))
                                {
                                    checkCommand.Parameters.AddWithValue("@FilePath", file.Path);
                                    checkCommand.Parameters.AddWithValue("@ComputerName", computerName);
                                    long exists = (long)checkCommand.ExecuteScalar();

                                    // 如果文件不存在，则插入
                                    if (exists == 0)
                                    {
                                        var fileInfo = new FileInfo(file.Path);
                                        command.Parameters.Clear();
                                        command.Parameters.AddWithValue("@FileName", file.Name);
                                        command.Parameters.AddWithValue("@FilePath", file.Path);
                                        command.Parameters.AddWithValue("@ComputerName", computerName);
                                        command.Parameters.AddWithValue("@FileExtension", file.Extension);
                                        command.Parameters.AddWithValue("@SearchDate", DateTime.Now);
                                        command.Parameters.AddWithValue("@FileSize", fileInfo.Exists ? fileInfo.Length : 0);
                                        command.Parameters.AddWithValue("@LastModified", fileInfo.Exists ? fileInfo.LastWriteTime : DateTime.MinValue);

                                        // 获取并存储图标数据
                                        byte[] iconData = file.GetIconData();
                                        command.Parameters.AddWithValue("@IconData", (object)iconData ?? DBNull.Value);

                                        command.ExecuteNonQuery();
                                    }
                                }
                            }
                        }
                        transaction.Commit();
                    }
                    catch
                    {
                        transaction.Rollback();
                        throw;
                    }
                }
            }
        }

        /// <summary>
        /// 加载搜索结果
        /// </summary>
        public List<WeChatFile> LoadSearchResults()
        {
            var results = new List<WeChatFile>();
            using (var connection = new SQLiteConnection(_connectionString))
            {
                connection.Open();
                string selectSql = @"
                    SELECT FileName, FilePath, ComputerName, FileExtension, SearchDate, FileSize, LastModified, IconData 
                    FROM SearchResults 
                    ORDER BY SearchDate DESC";

                using (var command = new SQLiteCommand(selectSql, connection))
                {
                    using (var reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            string filePath = reader.GetString(1);
                            string computerName = reader.GetString(2);
                            //if (File.Exists(filePath))
                            {
                                // 获取图标数据
                                byte[] iconData = null;
                                if (!reader.IsDBNull(7))
                                {
                                    iconData = (byte[])reader.GetValue(7);
                                }
                                // 使用带图标数据的构造函数创建WeChatFile对象
                                var file = new WeChatFile(
                                    filePath,
                                    false, // 不立即加载图标
                                    iconData // 传入保存的图标数据
                                );

                                file.SearchDate = reader.GetDateTime(4);
                                if (File.Exists(filePath))
                                    file.Size = reader.GetInt64(5);
                                file.LastModified = reader.GetDateTime(6);
                                file.ComputerName = computerName; // 设置计算机名

                                results.Add(file);
                            }
                        }
                    }
                }
            }
            return results;
        }
    }
}